from start import db
from datetime import datetime


# 文章与标签多对多中间表
post_tag = db.Table(
    'post_tags',
    db.Column('post_id', db.Integer, db.ForeignKey('post_db.id'), primary_key=True),
    db.Column('tag_id', db.Integer, db.ForeignKey('tag_db.id'), primary_key=True)
)

# 笔记与标签多对多中间表
note_tag = db.Table(
    'note_tags',
    db.Column('note_id', db.Integer, db.ForeignKey('note_db.id'), primary_key=True),
    db.Column('tag_id', db.Integer, db.ForeignKey('tag_db.id'), primary_key=True)
)


class BaseModel(db.Model):
    """ 基类模型 """
    __abstract__ = True  # 一般在基类模型中设置__abstract__ = True, 代表抽象语法类
    id = db.Column(db.Integer, primary_key=True, comment='id主键')
    add_time = db.Column(db.DateTime, default=datetime.now(), comment='创建时间')
    update_time = db.Column(db.DateTime, default=datetime.now(), onupdate=datetime.now(), comment='更新时间')


class Post(BaseModel):
    """ 文章表 """
    __tablename__ = 'post_db'
    title = db.Column(db.String(256), comment='标题')
    cover = db.Column(db.String(512), comment='文章封面')
    content_md = db.Column(db.Text, comment='markdown源内容')
    content_html = db.Column(db.Text, comment='html内容')
    read_count = db.Column(db.Integer, default=0, comment='阅读次数')
    drag_flag = db.Column(db.Boolean, default=False, comment='是否为草稿')  # default=0 默认值

    """关联"""
    category_id = db.Column(db.Integer, db.ForeignKey('category_db.id'), comment='关联分类表id')
    tags = db.relationship('Tag', secondary=post_tag, backref='posts')  # 文章与标签表多对多关联
    # cascade='delete' 一旦删除文章，文章下的所有评论全部随之删除
    comments = db.relationship('Comment', backref='post', cascade='delete')  # 文章与评论表一对多关联  cascade='delete'
    attachments = db.relationship('Attachment', backref='post')
    user_id = db.Column(db.Integer, db.ForeignKey('user_db.id'), comment='关联用户表id')




class Category(BaseModel):
    """ 分类表 """
    __tablename__ = 'category_db'
    name = db.Column(db.String(64), unique=True, comment='分类名')  # unique=True 保证分类名是唯一的

    """关联"""
    posts = db.relationship('Post', backref='category')  # 文章表和分类表关联
    notes = db.relationship('Note', backref='category')  # 笔记表和分类表关联


"""在设置表模型的时候, 有很多字段可能会重复, 可以抽离公共的字段"""


class Tag(BaseModel):
    """ 标签表 """
    __tablename__ = 'tag_db'
    name = db.Column(db.String(64), unique=True, comment='标签名')
    color = db.Column(db.String(64), comment='标签颜色')


class Note(BaseModel):
    """ 笔记表 """
    __tablename__ = 'note_db'
    title = db.Column(db.String(256), comment='笔记标题')
    content_md = db.Column(db.Text, comment='markdown源内容')
    content_html = db.Column(db.Text, comment='html内容')
    read_count = db.Column(db.Integer, default=0, comment='阅读次数')

    """关联"""
    category_id = db.Column(db.Integer, db.ForeignKey('category_db.id'), comment='关联分类表id')
    tags = db.relationship('Tag', secondary=note_tag, backref='note')
    attachments = db.relationship('Attachment', backref='note')  # 笔记表和附件表关联
    user_id = db.Column(db.Integer, db.ForeignKey('user_db.id'), comment='关联用户表id')


class Comment(BaseModel):
    """ 评论表 """
    __tablename__ = 'comment_db'
    contend = db.Column(db.Text, comment='评论内容')
    visitor_name = db.Column(db.String(64), comment='游客名')
    visitor_email = db.Column(db.String(64), comment='游客邮箱')
    visitor_ip = db.Column(db.String(50), comment='游客ip')
    visitor_address = db.Column(db.String(50), comment='游客地址')

    """关联"""
    post_id = db.Column(db.Integer, db.ForeignKey('post_db.id'), comment='关联文章表表id')
    # 可以根据 replied_id 判断此评论是顶级评论还是嵌套的评论
    replied_id = db.Column(db.Integer, db.ForeignKey('comment_db.id'), comment='回复的评论id')
    # 通过主评论可以拿到所有的子评论, 子评论可以通过 db.relationship中的backref可以拿到主评论
    # remote_side='Comment.id'  反向引用显示指定的是Comment.id, 因为是自关联的关系, 不加参数也会默认指向外键的id
    replied = db.relationship('Comment', backref='replies', remote_side='Comment.id', cascade='delete')

class Message(BaseModel):
    """ 留言表 """
    __tablename__ = 'message_db'
    content = db.Column(db.Text, comment='留言内容')
    visitor_name = db.Column(db.String(64), comment='游客名')
    visitor_email = db.Column(db.String(64), comment='游客邮箱')
    visitor_ip = db.Column(db.String(50), comment='游客ip')
    visitor_address = db.Column(db.String(50), comment='游客地址')


class Attachment(BaseModel):
    """ 附件表 """
    __tablename__ = 'attachment_db'
    file_name = db.Column(db.String(256), comment='文件名')
    file_size = db.Column(db.String(32), comment='文件大小')
    file_path = db.Column(db.String(256), comment='文件路径')

    """关联"""
    post_id = db.Column(db.Integer, db.ForeignKey('post_db.id'), comment='关联文章表id')
    note_id = db.Column(db.Integer, db.ForeignKey('note_db.id'), comment='关联笔记表id')